﻿
Partial Class WebForm_ManageProduct_ManageMaintainChannel
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack() Then
            Me.BindGV()
        End If
    End Sub

    Protected Sub ddlDivision_DataBound(ByVal sender As Object, ByVal e As System.EventArgs)
        Me.AddHeader(ddlDivision)
    End Sub

    Protected Sub ddlBrand_DataBound(ByVal sender As Object, ByVal e As System.EventArgs)
   
        Me.AddHeader(ddlBrand)

    End Sub

    Protected Sub ddlDivision_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        If ddlDivision.SelectedValue = "" Then
            ddlBrand.Items.Clear()
        End If
        ddlBrand.DataBind()

    End Sub
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Me.BindGV()
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
    Protected Sub btnImport_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.PreRender
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(btnImport)
    End Sub
    Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf FileUpload1.PostedFile.ContentType.ToString() <> "application/vnd.ms-excel" AndAlso FileUpload1.PostedFile.ContentType.ToString() <> "application/octet-stream" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls, csv) file!!")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable

        '檢查工作表名稱
        If ExcelImportCheck.LoadData(dt, newName, "Channel", IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
            Return
        Else
            Me.ImportChannelData = dt
        End If

        If Me.ImportChannelData.Rows.Count > 0 Then

            Dim sql As New MyStringBuilder
            Dim dat As New clsData

            For i As Integer = 0 To Me.ImportChannelData.Rows.Count - 1

                Me.ImportChannelData.Rows(i)("Brand") = Replace(Me.ImportChannelData.Rows(i)("Brand"), "'", "''")
                sql.Length = 0

                sql.AppendFormat("insert into Channel(Division,Brand,Channel) ")
                sql.AppendFormat("select '{0}' ", Me.ImportChannelData.Rows(i)("Division"))
                sql.AppendFormat(",'{0}' ", Me.ImportChannelData.Rows(i)("Brand"))
                sql.AppendFormat(",'{0}' ", Me.ImportChannelData.Rows(i)("Channel"))
                sql.AppendFormat("where not exists ( ")
                sql.AppendFormat("Select * From Channel ")
                sql.AppendFormat("Where Division = '{0}' ", Me.ImportChannelData.Rows(i)("Division"))
                sql.AppendFormat("And Brand = '{0}' ", Me.ImportChannelData.Rows(i)("Brand"))
                sql.AppendFormat("And Channel = '{0}' ", Me.ImportChannelData.Rows(i)("Channel"))
                sql.AppendFormat(") ")

                dat.AccData(sql)
            Next
            Me.BindGV()
            MyObj.ShowMessage("Import successful!!!")
        Else
            MyObj.ShowMessage("No data is available for import!!!")
        End If
    End Sub
    Private Sub AddHeader(ByVal ddlItem As DropDownList)
        ddlItem.Items.Insert(0, MyObj.GetDDLSelectAllItem())
    End Sub

    Public Sub BindGV()
        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.Length = 0
        sql.AppendFormat("Select * from Channel ")
        sql.AppendFormat("where 1=1 ")
        If ddlDivision.SelectedValue <> "" Then
            sql.AppendFormat("and Division = '{0}' ", ddlDivision.SelectedValue)
        End If
        If ddlBrand.SelectedValue <> "" Then
            sql.AppendFormat("and Brand = '{0}' ", ddlBrand.SelectedValue.Replace("'", "''"))
        End If
        sql.AppendFormat("order by Division,Brand ")

        gv.DataSource = dat.GetData(sql)
        gv.DataBind()
    End Sub
    Property ImportChannelData() As Data.DataTable
        Get
            Return ViewState("ImportChannelData")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("ImportChannelData") = value
        End Set
    End Property

End Class
